Click Here!
home account info subscribe login search My ITKnowledge FAQ/help site map contact us


 
Brief Full
 Advanced
      Search
 Search Tips
To access the contents, click the chapter and section titles.

Oracle Performance Tuning and Optimization
(Publisher: Macmillan Computer Publishing)
Author(s): Edward Whalen
ISBN: 067230886x
Publication Date: 04/01/96

Bookmark It

Search this book:
 
Previous Table of Contents Next


By applying referential integrity constraints to the DOGS and BREEDS tables, you can guarantee that the DOGS table does not reference any value for a dog’s breed that does not exist. This constraint is applied by first adding BREED as a primary key in the BREEDS table:

SQL> ALTER TABLE breeds
 2      ADD PRIMARY KEY (breed);

Table altered.

Then you add BREED as a foreign key in the DOGS table:

SQL> ALTER TABLE dogs
 2      ADD FOREIGN KEY (breed) REFERENCES breeds(breed);

Table altered.

After you add these constraints, any attempt to add an additional entry to the DOGS table that references a nonexistent value in the BREEDS table results in the following error:

SQL> INSERT INTO dogs
  2  ( dogname, age, breed, owner )
 3  VALUES
   4  ( 'Lacy', 5, 12, 'Letterman' );
INSERT INTO dogs

ERROR at line 1:
ORA-02291: integrity constraint (ED.SYS_C00392) violated - parent
key not found

By correcting the error, the INSERT operation will succeed:

SQL> INSERT INTO dogs
  2  ( dogname, age, breed, owner )
 3  VALUES
   4  ( 'Lacy', 5, 2, 'Letterman' );

1 row created.

By using referential integrity constraints, you reduce the chance of errors and ensure the integrity of your data. Some types of data checking (such as verifying that a field is numeric or verifying that all fields are entered) may be better done in the application code; however, in most other cases, checking is handled more efficiently by Oracle. A good rule of thumb is that if you have to issue an SQL statement to verify referential integrity, you can better protect your data with a referential integrity constraint.

Integrity Constraints

By using the features of the database, you can reduce processing at the application or the database level. Some integrity constraints can verify that your data conforms to requirements you set up in your tables. Following is a list of a few of these constraints:

  NOT NULL. The NOT NULL constraint specifies that a column must have a value associated with it. This is useful when the absence of a value can cause data integrity problems. This constraint is sometimes used with the UNIQUE constraint to ensure not only that the value exists and is valid, but that the value is unique.
  UNIQUE. The UNIQUE key constraint is typically used in the definition of the table’s primary key. The UNIQUE key constraint specifies that the values inserted into the column or columns must be unique. Unique keys differ from primary keys in that they are not used to uniquely identify a row in the database, but rather to avoid duplications within the column. NULL values can be inserted unless the NOT NULL constraint is also present for that column.
  PRIMARY. The primary key in a table is used to uniquely identify a row in a table and to avoid duplication in the column or columns designated as the primary key. Although a primary key can be a composite of more than one row, this approach is discouraged. Because the PRIMARY key is used to identify the row in the table, it should be unique and somewhat stable. The values in the column used as the primary key should not change, contain NULLs, or be long. Short numeric values are ideal for primary keys; sequences are typically used to provide the key values.
  FOREIGN. The foreign key in a table is used to reference a primary key in another table and is used to maintain the relationship between these two tables. When you reference a primary key in another table as a foreign key, you maintain the relationship as well as the integrity of the data.
  CHECK. The CHECK constraint can be used to enforce business rules that cannot be enforced by any other type of constraint. For example, this type of business rule may be checking that a value does not exceed a maximum value or fall below a minimum value. Other examples of nonstandard business rules are checking that a column value is not larger than another column value or that only certain discrete values are used. You can use the CHECK constraint to handle these kinds of miscellaneous functions.


Previous Table of Contents Next


Products |  Contact Us |  About Us |  Privacy  |  Ad Info  |  Home

Use of this site is subject to certain Terms & Conditions, Copyright © 1996-2000 EarthWeb Inc.
All rights reserved. Reproduction whole or in part in any form or medium without express written permission of EarthWeb is prohibited.